-- EXEC SP_GET_FGI_PRODUCT_IDS 1977

ALTER  PROCEDURE SP_GET_FGI_PRODUCT_IDS ( @FG_PROCESS_STEP_ID INT ) AS
BEGIN
	DECLARE @FGI_PROCESSSTEP_ID INT
	DECLARE @FGI_PRODUCT_ID INT
	DECLARE @FGI_PROCESSSTEP_IDS AS CURSOR
	DECLARE @RESULTS_TABLE TABLE ( PRODUCT_ID INT, PROCESSSTEP_ID INT )
	SET @FGI_PROCESSSTEP_IDS = CURSOR FOR SELECT FGI_PROCESS_STEP_ID FROM FG_TO_FGI_MAP_TABLE 
					WHERE FG_PROCESS_STEP_ID = @FG_PROCESS_STEP_ID

	OPEN @FGI_PROCESSSTEP_IDS
	FETCH NEXT FROM @FGI_PROCESSSTEP_IDS INTO @FGI_PROCESSSTEP_ID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @FGI_PRODUCT_ID = PRODUCT_ID FROM PRODUCT WHERE PROCESSSTEP_ID = @FGI_PROCESSSTEP_ID
		INSERT INTO @RESULTS_TABLE( PRODUCT_ID, PROCESSSTEP_ID) VALUES ( @FGI_PRODUCT_ID,@FGI_PROCESSSTEP_ID )
		FETCH NEXT FROM @FGI_PROCESSSTEP_IDS INTO @FGI_PROCESSSTEP_ID				
	END
	CLOSE @FGI_PROCESSSTEP_IDS
	DEALLOCATE @FGI_PROCESSSTEP_IDS
	
	SELECT * FROM @RESULTS_TABLE
END


